package com.zxy.supplier_system.server.repository

import com.zxy.supplier_system.server.controller.MarketStatisticController
import com.zxy.supplier_system.server.entity.Goods
import com.zxy.supplier_system.server.entity.GoodsUnit
import com.zxy.supplier_system.server.entity.Market
import com.zxy.supplier_system.server.entity.MarketAccount
import com.zxy.supplier_system.server.entity.MarketDeliveryOrder
import com.zxy.supplier_system.server.entity.MarketDeliveryOrderItem
import com.zxy.supplier_system.server.entity.MarketSaleDocument
import com.zxy.supplier_system.server.entity.MarketSaleDocumentItem
import com.zxy.supplier_system.server.entity.Procurement
import com.zxy.supplier_system.server.entity.ProcurementItem
import com.zxy.supplier_system.server.entity.SubGoods
import com.zxy.supplier_system.server.entity.SystemService
import com.zxy.supplier_system.server.repository.MarketStatisticRepository.StatisticAuditMarketSaleDocumentByMarketGroupByYearMonthData
import com.zxy.supplier_system.server.repository.MarketStatisticRepository.StatisticAuditedMarketOrderData
import com.zxy.supplier_system.server.utils.joinOnce
import jakarta.persistence.EntityManager
import jakarta.persistence.PersistenceContext
import jakarta.persistence.Tuple
import org.springframework.data.jpa.domain.Specification
import org.springframework.stereotype.Repository
import java.math.BigDecimal
import kotlin.jvm.java

interface MarketStatisticRepository {

    fun statisticAuditedMarketOrderByMarketStatisticRequest(
        marketId: Int,
        request: MarketStatisticController.MarketStatisticRequest,
        systemService: SystemService,
    ): StatisticAuditedMarketOrderData

    data class StatisticAuditedMarketOrderData(
        /**
         * 总送货零售价
         */
        val totalPrice: BigDecimal,
        /**
         * 送货单数量
         */
        val deliveryOrderCount: Int,
        /**
         * 总成本
         */
        val totalCost: BigDecimal,
    )

    fun statisticAuditedMarketSaleDocumentByMarketStatisticRequest(
        marketId: Int,
        request: MarketStatisticController.MarketStatisticRequest,
        systemService: SystemService,
    ): BigDecimal

    fun statisticAuditMarketSaleDocumentByMarketGroupByYearMonth(marketId: Int,
                                                        request: MarketStatisticController.MarketStatisticRequest,
                                                        systemService: SystemService,):List<StatisticAuditMarketSaleDocumentByMarketGroupByYearMonthData>

    data class StatisticAuditMarketSaleDocumentByMarketGroupByYearMonthData(
        val year:Int,
        val month:Int,
        val money:BigDecimal,
    )
}

@Repository
class MarketStatisticRepositoryImpl : MarketStatisticRepository {

    @PersistenceContext
    private lateinit var entityManager: EntityManager
    override fun statisticAuditedMarketOrderByMarketStatisticRequest(
        marketId: Int,
        request: MarketStatisticController.MarketStatisticRequest,
        systemService: SystemService,
    ): StatisticAuditedMarketOrderData {
        val criteriaBuilder = entityManager.criteriaBuilder
        val query = criteriaBuilder.createQuery()
        val root = query.from(MarketDeliveryOrderItem::class.java)
        val marketDeliveryOrderJoin = root.joinOnce<MarketDeliveryOrderItem, MarketDeliveryOrder>("order")
        val goodsJoin = root.joinOnce<MarketDeliveryOrderItem, Goods>("goods")
        val marketJoin = marketDeliveryOrderJoin.joinOnce<MarketDeliveryOrder, Market>("market")
        val unitJoin = root.joinOnce<MarketDeliveryOrderItem, GoodsUnit>("unit")
        val subGoodsJoin = root.joinOnce<MarketDeliveryOrderItem, SubGoods>("subGoods")

        val sumCostQuery = query.subquery(BigDecimal::class.java)
        val sumCostRoot = sumCostQuery.from(ProcurementItem::class.java)
        sumCostQuery.select(sumCostRoot.get<BigDecimal>("purchasePrice"))

        val sumCostInnerQuery = query.subquery(Int::class.java)
        val sumCostInnerRoot = sumCostInnerQuery.from(ProcurementItem::class.java)
        val sumCostInnerSubGoodsJoin = sumCostInnerRoot.joinOnce<ProcurementItem, SubGoods>("subGoods")
        val sumCostInnerGoodsJoin = sumCostInnerRoot.joinOnce<ProcurementItem, Goods>("goods")
        val sumCostInnerProcurementJoin = sumCostInnerRoot.joinOnce<ProcurementItem, Procurement>("procurement")
        sumCostInnerQuery.where(
            criteriaBuilder.and(
                criteriaBuilder.selectCase<Boolean>().`when`(criteriaBuilder.isNull(subGoodsJoin), true)
                    .otherwise(
                        criteriaBuilder.equal(
                            subGoodsJoin.get<Int>("id"),
                            sumCostInnerSubGoodsJoin.get<Int>("id")
                        )
                    ),
                criteriaBuilder.equal(goodsJoin.get<Int>("id"), sumCostInnerGoodsJoin.get<Int>("id"))
            ),
        )
        sumCostInnerQuery.select(
            criteriaBuilder.max<Int>(
                sumCostInnerProcurementJoin
                    .get<Int>("id")
            )
        )

        sumCostQuery.where(criteriaBuilder.equal(sumCostRoot.get<Int>("id"), sumCostInnerQuery))

        val amountWithBasicUnit = criteriaBuilder.prod(
            root.get<Int>("amount"),
            criteriaBuilder.function("greatest",Int::class.java,criteriaBuilder.coalesce(unitJoin.get<Int>("basicMultiple"),1), criteriaBuilder.literal(1)),
        )
        query.multiselect(
            criteriaBuilder.countDistinct(marketDeliveryOrderJoin.get<Int>("id")),
            criteriaBuilder.sum<BigDecimal>(
                criteriaBuilder.prod<BigDecimal>(
                    goodsJoin.get<BigDecimal>("price"),
                    amountWithBasicUnit.`as`(
                        BigDecimal::class.java
                    )
                )
            ),
            criteriaBuilder.sum<BigDecimal>(
                criteriaBuilder.prod<BigDecimal>(
                    criteriaBuilder.coalesce(sumCostQuery, goodsJoin.get<BigDecimal>("costPrice")),
                    amountWithBasicUnit.`as`(BigDecimal::class.java),
                )
            )
        )
        query.where(
            criteriaBuilder.equal(marketJoin.get<Int>("id"), marketId),
            Specification.allOf(
                MarketDeliveryOrderItemRepository.Specifications.systemServiceEquals(systemService)
                    ,
                MarketDeliveryOrderItemRepository.Specifications.orderCreatedDateTimeGreaterThanOrEqualTo(request.startYearMonth?.let {
                    it.atDay(1)
                }),
                MarketDeliveryOrderItemRepository.Specifications.orderCreatedDateTimeLessThan(request.endYearMonth?.let {
                    it.atEndOfMonth()
                }),
                MarketDeliveryOrderItemRepository.Specifications.goodsBrandIn(request.brandIds),
            ).toPredicate(root, query, criteriaBuilder),
         )
        val result = entityManager.createQuery(query).singleResult as Array<Any>
        return StatisticAuditedMarketOrderData(
            (result[1] as BigDecimal?)?: BigDecimal.ZERO,
            (result[0] as Long).toInt(),
            (result[2] as BigDecimal?)?: BigDecimal.ZERO,
        )
    }

    override fun statisticAuditedMarketSaleDocumentByMarketStatisticRequest(
        marketId: Int,
        request: MarketStatisticController.MarketStatisticRequest,
        systemService: SystemService
    ): BigDecimal {
        val criteriaBuilder = entityManager.criteriaBuilder
        val query = criteriaBuilder.createQuery()

        val root = query.from(MarketSaleDocumentItem::class.java)
        val marketSaleDocumentJoin = root.joinOnce<MarketSaleDocumentItem, MarketSaleDocument>("document")
        val marketAccountJoin = marketSaleDocumentJoin.joinOnce<MarketSaleDocument, MarketAccount>("account")
        val marketJoin = marketAccountJoin.joinOnce<MarketAccount, Market>("market")
        query.where(
            criteriaBuilder.equal(marketJoin.get<Int>("id"), marketId), Specification.allOf(
                MarketSaleDocumentItemRepository.Specifications.documentYearMonthGreaterThanOrEqualTo(request.startYearMonth),
                MarketSaleDocumentItemRepository.Specifications.documentYearMonthLessThanOrEqualTo(request.endYearMonth),
                MarketSaleDocumentItemRepository.Specifications.goodsBrandIn(request.brandIds),
                MarketSaleDocumentItemRepository.Specifications.systemServiceEquals(systemService),
            ).toPredicate(root, query, criteriaBuilder),
        )

        query.select(criteriaBuilder.coalesce(criteriaBuilder.sum(root.get<BigDecimal>("totalFee")),criteriaBuilder.literal(0)))
        return entityManager.createQuery(query).singleResult as BigDecimal
    }



    override fun statisticAuditMarketSaleDocumentByMarketGroupByYearMonth(
        marketId: Int,
        request: MarketStatisticController.MarketStatisticRequest,
        systemService: SystemService
    ): List<StatisticAuditMarketSaleDocumentByMarketGroupByYearMonthData> {
        val criteriaBuilder = entityManager.criteriaBuilder
        val query = criteriaBuilder.createQuery(Tuple::class.java)

        val root = query.from(MarketSaleDocumentItem::class.java)
        val marketSaleDocumentJoin = root.joinOnce<MarketSaleDocumentItem, MarketSaleDocument>("document")
        val marketAccountJoin = marketSaleDocumentJoin.joinOnce<MarketSaleDocument, MarketAccount>("account")
        val marketJoin = marketAccountJoin.joinOnce<MarketAccount, Market>("market")
        val year = marketSaleDocumentJoin.get<Int>("year")
        val month = marketSaleDocumentJoin.get<Int>("month")
        query.groupBy(year, month)
        query.orderBy(criteriaBuilder.asc(year),criteriaBuilder.asc(month))
        query.where(
            criteriaBuilder.equal(marketJoin.get<Int>("id"), marketId), Specification.allOf(
                MarketSaleDocumentItemRepository.Specifications.documentYearMonthGreaterThanOrEqualTo(request.startYearMonth),
                MarketSaleDocumentItemRepository.Specifications.documentYearMonthLessThanOrEqualTo(request.endYearMonth),
                MarketSaleDocumentItemRepository.Specifications.goodsBrandIn(request.brandIds),
                MarketSaleDocumentItemRepository.Specifications.systemServiceEquals(systemService),
            ).toPredicate(root, query, criteriaBuilder),
        )
        query.multiselect(marketSaleDocumentJoin.get<Int>("year"),marketSaleDocumentJoin.get<Int>("month"),criteriaBuilder.sum<BigDecimal>(root.get<BigDecimal>("totalFee")))
        val resultList = entityManager.createQuery(query).resultList
        return resultList.map { item ->
            val year = item.get(0) as Int
            val month = item.get(1) as Int
            val fee = item.get(2) as BigDecimal
            StatisticAuditMarketSaleDocumentByMarketGroupByYearMonthData(year,month,fee)
        }
    }


}